#!/usr/bin/env python
# coding=utf-8
"""
@Author  : youjia - 卞志伟
@file    : comment_specify.py
@contact : bianzhiwei@iyoujia.com
@time    : 2019-06-27 17:46 
@Desc    : 里面都是评论的
@Software: PyCharm
"""
import os
import sys

# 当前文件的路径
pwd, filename = os.path.split(os.path.abspath(__file__))
# 当前文件的父路径
father_path = os.path.abspath(os.path.dirname(pwd) + os.path.sep + ".")
# 当前文件的前两级目录
grader_father = os.path.abspath(os.path.dirname(pwd) + os.path.sep + "..")
sys.path.append(pwd)
sys.path.append(father_path)
sys.path.append(grader_father)

import pandas as pd
from report_system.utils import db_util
from report_system.utils import df_util
from report_system.utils import date_util
from report_system.utils.num_util import percentage
from report_system.utils.log_util import log
from report_system.utils.enum_util import tujia_dis_remove
from report_system.report_specify.service_specify import tujia_dis


def comment_overview_sql(st, et):
    """
    评论数据总览
    :param st:
    :param et:
    :return:
    """
    sql = """select 
            case group_concat(distinct h.own_type order by h.own_type)
                when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
            if(coalesce(uo.name,'全国')='全国','全国',group_concat(distinct city.short_name) ) 城市,
            coalesce(uo.name,'全国') 门店,
            count(1) 离店订单数,
            sum(if(ct.content is not null,1,0)) 评论数,
            sum(if(ct.score < 4,1,0))  渠道差评数,
            sum(if(ct.score < 5 and ct.score >= 4,1,0))  渠道中评数,
            sum(if(ct.score >= 5,1,0))  渠道好评数,
            concat(round(sum(if(ct.content is not null,1,0))/count(1)*100,2),'%') 评论率,
            concat(round(sum(if(ct.score >= 5,1,0))/sum(if(ct.content is not null,1,0))*100,2),'%') 好评率,
            concat(round(sum(if(ct.score < 5 and ct.score >= 4,1,0))/sum(if(ct.content is not null,1,0))*100,2),'%') 中评率,
            concat(round(sum(if(ct.score  < 4,1,0))/sum(if(ct.content is not null,1,0))*100,2),'%') 差评率,
            sum(if(ct.content is not null and bo.order_channel=3,1,0)) 途家评论数,
            sum(if(ct.content is not null and bo.order_channel=20,1,0)) 爱彼迎评论数,
            sum(if(ct.content is not null and bo.order_channel=25,1,0)) 榛果评论数,
            concat(round(sum(if(ct.content is not null and bo.order_channel=3,1,0))/sum(if(ct.content is not null,1,0))*100,2),'%') 途家评论占比,
            concat(round(sum(if(ct.content is not null and bo.order_channel=20,1,0))/sum(if(ct.content is not null,1,0))*100,2),'%') 爱彼迎评论占比,
            concat(round(sum(if(ct.content is not null and bo.order_channel=25,1,0))/sum(if(ct.content is not null,1,0))*100,2),'%') 榛果评论占比

            from youjia.orders bo
            LEFT JOIN youjia.comment ct on ct.order_id = bo.id AND ct.comment_type=0
			LEFT JOIN youjia.order_extend d ON bo.id=d.order_id
            LEFT JOIN youjia.city on city.id = bo.city_id
            LEFT JOIN youjia.users u ON d.off_manager_id=u.id
            LEFT JOIN youjia.lod_house h ON h.lodge_id=bo.lodgeunit_id
           --  LEFT JOIN youjia.user_org_service_center usc ON usc.sid=h.service_id
            LEFT JOIN youjia.lodge l ON bo.lodgeunit_id=l.id
            left join youjia.user_org uo on uo.id = h.work_id and uo.is_delete = 0
            
            where bo.city_id <> 376
            and bo.pay_state = 1
            and bo.confirm_status=1
            AND bo.cancel_status=0 and bo.brush_status<>1
            AND bo.order_type in (0)
            and bo.order_channel in (3,20,25)
            and date(bo.checkout_day) between '{st}' and '{et}'
           -- AND h.service_id IS NOT NULL
            AND d.tenant_mobile not LIKE '%转%'
            group by h.own_type,uo.name with rollup""".format(st=st, et=et)
    #      -- AND d.account_mobile  REGEXP "^[1][345678][0-9]{9}$"
    # print(sql)
    return pd.read_sql_query(sql, db_util.con_youjia)


def bad_comment(st, et):
    """
    获取差评明细
    :return: 城市、门店、房源ID、房源地址、房客电话、房客姓名、订单ID、渠道、评论时间、总分、房屋卫生、管家服务、交通位置、室内设施、周边环境、评论详情
    """
    sql = """ select 
        case group_concat(distinct lh.own_type order by lh.own_type)
                when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
        city.short_name 城市,
        org.name 门店,
        concat(lh.lodge_id,'') 房源id,
        concat(lh.id,'') 房屋id,
        group_concat(distinct u1.realname) 线上销售,
        group_concat(distinct u2.realname) 线下管家,
        lh.title_sub 房源名称,
        lh.address 地址,
        d.account_mobile 房客电话,
        d.tenant_name 房客姓名,
        concat(bo.id,'') 订单ID ,
        sc.name 渠道,
        ct.create_time 评论时间, 
        ct.score 总分,
        ct.sanitation_s 房屋卫生,
        ct.service_s 管家服务,
        ct.traffic_s 交通位置,
        ct.facilities_s 室内设施,
        ct.environment_s 周边环境,
        ct.content 评论详情 
        from youjia.orders bo 
        left join youjia.comment ct on ct.order_id = bo.id  AND ct.comment_type=0
        LEFT JOIN youjia.order_extend d ON bo.id=d.order_id
        left join youjia.city on city.id = bo.city_id
        LEFT JOIN youjia.users u ON d.off_manager_id=u.id
        LEFT JOIN youjia.lod_house lh ON lh.lodge_id=bo.lodgeunit_id
        LEFT JOIN youjia.user_org org ON org.id=lh.work_id and  org.is_delete = 0       
        LEFT JOIN youjia.lodge l ON bo.lodgeunit_id=l.id
        left join youjia_common.sales_channel sc on sc.id = bo.order_channel
        left join youjia.orders_distribute ods on ods.order_id = bo.id
        left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
        LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
        left join youjia.user_house_relation uhr2 on uhr2.house_id = lh.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
        LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
        
        where bo.city_id <> 376 
        and bo.pay_state=1
        AND bo.confirm_status=1
        AND bo.cancel_status=0
        AND bo.order_type in (0)
        and ods.dis_sec_channel is null
        and bo.order_channel in (3,20,25)
        AND DATE(bo.checkout_day) BETWEEN '{st}'  AND  '{et}'
        AND d.tenant_mobile not LIKE '%转%'
        and ct.score < 4
        and ct.score <> ''
        and ct.score is not null
        group by bo.id;""".format(st=st, et=et)
    return pd.read_sql_query(sql, db_util.con_youjia)


def good_comment(st, et):
    """
    好评明细
    :param st:
    :param et:
    :return:
    """
    sql = """ 
        select city.short_name 城市,
        case group_concat(distinct h.own_type order by h.own_type)
                when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
        org.name 门店,
        concat(h.lodge_id,'') 房源id,
        concat(h.id,'') 房屋id,
        group_concat(distinct u1.realname) 线上销售,
        group_concat(distinct u2.realname) 线下管家,
        h.title_sub 房源名称,
        h.address 地址,
        concat( bo.id,'') 订单ID ,
        sc.name 渠道,
        concat(bo.create_time,'') 创建时间, 
        concat(bo.checkin_day,'') 入住时间,
        concat(bo.checkout_day,'') 离店时间,
        ct.create_time 评论时间, 
        ct.score 总分,
        d.tenant_name 房客姓名,
        ct.create_time 评论时间, 
        ct.score 总分,
        ct.sanitation_s 房屋卫生,
        ct.service_s 管家服务,
        ct.traffic_s 交通位置,
        ct.facilities_s 室内设施,
        ct.environment_s 周边环境,
        ct.content 评论详情
        
        from youjia.orders bo 
        left join youjia.comment ct on ct.order_id = bo.id  AND ct.comment_type=0
        LEFT JOIN youjia.order_extend d ON bo.id=d.order_id
        left join youjia.orders_distribute ods on ods.order_id = bo.id
        left join youjia.city on city.id = bo.city_id
        LEFT JOIN youjia.users u ON d.off_manager_id=u.id
        LEFT JOIN youjia.lod_house h ON h.lodge_id=bo.lodgeunit_id
        LEFT JOIN youjia.user_org org ON org.id=h.work_id and org.is_delete = 0      
        LEFT JOIN youjia.lodge l ON bo.lodgeunit_id=l.id
        left join youjia_common.`sales_channel` sc on sc.id = bo.order_channel
        
         left join youjia.user_house_relation uhr1 on uhr1.house_id = h.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
        LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
        left join youjia.user_house_relation uhr2 on uhr2.house_id = h.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
        LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
        
        where bo.city_id <> 376 
        and bo.pay_state=1
        AND bo.confirm_status=1
        AND bo.cancel_status=0
        AND bo.order_type in (0)
        and ods.dis_sec_channel is null
        and bo.brush_status <> 1
        and bo.order_channel in (3,20,25)
        AND DATE(bo.checkout_day) BETWEEN '{st}'  AND  '{et}'
        AND d.tenant_mobile not LIKE '%转%'
        and ct.score >= 5
        and ct.score <> ''
        and ct.score is not null
        group by bo.id;""".format(st=st, et=et)
    return pd.read_sql_query(sql, db_util.con_youjia)


def no_comment(st, et):
    """
    离店未评价
    :param st:
    :param et:
    :return:
    """
    sql = """select 
            case group_concat(distinct h.own_type order by h.own_type)
                when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
            city.short_name 城市,
             org.name 门店,
            
            concat(h.lodge_id,'') 房源id,
            concat(h.id,'') 房屋id,
            group_concat(distinct u1.realname) 线上销售,
            group_concat(distinct u2.realname) 线下管家,
            h.title_sub 房源名称,
            h.address 地址,
            d.account_mobile 房客电话,
            d.tenant_name 房客姓名,
            concat( bo.id,'') 订单ID ,
            sc.name 渠道,
            concat(bo.checkout_day,'') 离店时间
            from youjia.orders bo 
            left join youjia.comment ct on ct.order_id = bo.id  AND ct.comment_type=0
            LEFT JOIN youjia.order_extend d ON bo.id=d.order_id
            left join youjia.orders_distribute ods on ods.order_id = bo.id
            left join youjia.city on city.id = bo.city_id
            LEFT JOIN youjia.users u ON d.off_manager_id=u.id
            LEFT JOIN youjia.lod_house h ON h.lodge_id=bo.lodgeunit_id
            LEFT JOIN youjia.user_org org ON org.id=h.work_id and org.is_delete = 0       
            LEFT JOIN youjia.lodge l ON bo.lodgeunit_id=l.id
            left join youjia_common.`sales_channel` sc on sc.id = bo.order_channel
        
            left join youjia.user_house_relation uhr1 on uhr1.house_id = h.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
            LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
            left join youjia.user_house_relation uhr2 on uhr2.house_id = h.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
            LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
            
            where bo.city_id <> 376 
            and bo.pay_state=1
            AND bo.confirm_status=1
            AND bo.cancel_status=0
            AND bo.order_type in (0)
            and bo.brush_status <> 1
            and ods.dis_sec_channel is null
            and bo.order_channel in (3,20,25)
            AND DATE(bo.checkout_day) BETWEEN '{st}'  AND  '{et}'
            AND d.tenant_mobile not LIKE '%转%'
            and (ct.score = '' or ct.score is null)
            group by bo.id;""".format(st=st, et=et)
    df = pd.read_sql_query(sql, db_util.con_youjia)
    tujia_dis_df = tujia_dis()
    log.info("获取途家二次分销渠道完成！！")
    df = pd.merge(df, tujia_dis_df, how='left')
    log.info("去除途家二次分销渠道 {}！！".format(tujia_dis_remove))
    df = df[~df.二次分销渠道.isin(tujia_dis_remove)]
    return df


def all_order_comment(st, et, own_type=(1, 2, 3), channel_ids=[3, 20, 25], have_brush=False):
    """
    所有离店订单评论明细
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :param have_brush: 是否包含刷单
    :return:
    """
    brush_add = "" if have_brush else " and b.brush_status<>1 "

    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)

    if isinstance(channel_ids, str) or isinstance(channel_ids, int):
        channel_ids = '({channel_ids})'.format(channel_ids=channel_ids)
    if isinstance(channel_ids, list):
        if channel_ids.__len__() == 1:
            channel_ids = '({channel_ids})'.format(channel_ids=channel_ids[0])
        else:
            channel_ids = tuple(channel_ids)
    sql = """SELECT  
        case group_concat(distinct h.own_type order by h.own_type)
            when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
        if(COALESCE(b.id ,'全国')='全国','全国', group_concat(distinct uo3.name))  大区,
        if(COALESCE(b.id ,'全国')='全国','全国', group_concat(distinct c.short_name))  城市,
        ifnull(if(COALESCE(b.id ,'全国')='全国','全国', group_concat(distinct org.name)),'门店为空') 门店,
        if(COALESCE(b.id ,'全国')='全国','全国', group_concat(distinct u1.realname order by u1.realname)) 线上销售,
        if(COALESCE(b.id ,'全国')='全国','全国', group_concat(distinct u2.realname order by u2.realname)) 线下管家,
        CONCAT(COALESCE(b.id ,'全国'),'') 订单ID,
        if(COALESCE(b.id ,'全国')='全国','全国', CONCAT(group_concat(distinct b.third_id,''))) 第三方订单ID,
        if(COALESCE(b.id ,'全国')='全国','全国', group_concat(distinct sc.`name`)) 渠道,
        if(COALESCE(b.id ,'全国')='全国','全国', CONCAT(group_concat(distinct b.checkout_day,''))) 离店时间,
        if(COALESCE(b.id ,'全国')='全国','全国',CONCAT(group_concat(distinct h.lodge_id,''))) 房源id,
        if(COALESCE(b.id ,'全国')='全国','全国',CONCAT(group_concat(distinct d.house_id,''))) 房屋id,
        if(COALESCE(b.id ,'全国')='全国','全国',group_concat(distinct h.title)) 房屋标题,
        if(COALESCE(b.id ,'全国')='全国','全国',group_concat(distinct h.address)) 地址,
        COUNT(distinct b.id) 离店订单数,
        round(avg(t.score),2) 总分,
        count(distinct IF(t.score>=5,b.id,null)) 好评数,
        count(distinct IF(t.score>=5 and b.order_channel=3,b.id,null)) 途家好评数,
        count(distinct IF(t.score>=5 and b.order_channel=20,b.id,null)) 爱彼迎好评数,
        count(distinct IF(t.score>=5 and b.order_channel=25,b.id,null)) 榛果好评数,
        count(distinct IF(t.score>=4 AND t.score<5,b.id,null)) 中评数,
        count(distinct IF(t.score<4 and t.content IS NOT null,b.id,null)) 差评数,
        count(distinct IF(b.order_channel=3 AND t.content IS NOT null,b.id,null)) 途家评数,
        count(distinct IF(b.order_channel=20 AND t.content IS NOT null,b.id,null)) 爱彼迎评数,
        count(distinct IF(b.order_channel=25 AND t.content IS NOT null,b.id,null)) 榛果评数,
        -- count(distinct IF(t.content IS NOT null ,b.id,null)) 评论数,
        count(distinct IF(t.content IS null ,b.id,null)) 未评论数
        FROM orders b
        LEFT JOIN order_detail d ON b.id=d.order_id
        LEFT JOIN youjia.order_extend e ON b.id=e.order_id
        LEFT JOIN lod_house h ON d.house_id=h.id
        LEFT JOIN city c ON c.id=h.city_id
        LEFT JOIN user_org_service_center usc ON h.service_id=usc.sid
        LEFT JOIN youjia_common.sales_channel sc ON sc.id=b.order_channel
        LEFT JOIN youjia.`comment` t ON b.id=t.order_id  AND t.comment_type=0
        LEFT JOIN user_org org ON h.work_id=org.id and org.is_delete = 0
		left join youjia.user_org uo2 on uo2.id=org.parent_id and uo2.is_delete = 0
		left join youjia.user_org uo3 on uo3.id=uo2.parent_id and uo3.is_delete = 0
        
        left join youjia.user_house_relation uhr1 on uhr1.house_id = h.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
        LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
        left join youjia.user_house_relation uhr2 on uhr2.house_id = h.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
        LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
        WHERE  b.city_id <> 376
        and h.city_id <> 376
        and b.pay_state = 1
        and h.own_type in {own_type}
        and b.confirm_status=1
        AND b.cancel_status=0 
        {brush_add}
        AND b.order_type = 0 
        and b.order_channel in {channel_ids}
        AND DATE(b.checkout_day) BETWEEN '{st}'  AND  '{et}'
        AND e.tenant_mobile not LIKE '%转%'
        -- AND h.service_id IS NOT NULL
        
        GROUP BY h.own_type,b.id
        with rollup
    """.format(st=st, et=et, own_type=own_type, channel_ids=channel_ids, brush_add=brush_add)
    # print(sql)
    df = pd.read_sql_query(sql, db_util.con_youjia)
    tujia_dis_df = tujia_dis()
    log.info("获取途家二次分销渠道完成！！")
    df = pd.merge(df, tujia_dis_df, how='left')

    dis_remove_df = df[df.二次分销渠道.isin(tujia_dis_remove)]
    dis_remove_len = len(dis_remove_df)
    log.info("去除途家二次分销渠道的长度: {}！！".format(dis_remove_len))

    log.info("去除途家二次分销渠道 {}！！".format(tujia_dis_remove))
    df = df[~df.二次分销渠道.isin(tujia_dis_remove)]
    df['离店订单数'] = df.apply(lambda row: row['离店订单数'] - dis_remove_len if row['订单ID'] == '全国' else row['离店订单数']
                           , axis=1)
    df['未评论数'] = df.apply(lambda row: row['未评论数'] - dis_remove_len if row['订单ID'] == '全国' else row['未评论数'], axis=1)
    df = df_util.df_sort(df, '离店订单数')
    return df


def all_order_comment_house(st, et, own_type=(1, 2, 3), channel_ids=[3, 20, 25], have_long=False):
    """
    所有离店订单评论明细
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :return:
    """
    sql_add = "" if have_long else 'and bo.order_type <> 3'

    if not isinstance(own_type, tuple) and not isinstance(own_type, list):
        own_type = '({own_type})'.format(own_type=own_type)
    if isinstance(channel_ids, str) or isinstance(channel_ids, int):
        channel_ids = '({channel_ids})'.format(channel_ids=channel_ids)
    if isinstance(channel_ids, list):
        if channel_ids.__len__() == 1:
            channel_ids = '({channel_ids})'.format(channel_ids=channel_ids[0])
        else:
            channel_ids = tuple(channel_ids)
    sql = """SELECT  
        case group_concat(distinct lh.own_type order by lh.own_type)
            when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
		if(COALESCE(lh.id ,'全国')='全国','全国', group_concat(distinct uo3.name))  大区,
        if(COALESCE(lh.id ,'全国')='全国','全国', group_concat(distinct c.short_name))  城市,
        ifnull(if(COALESCE(lh.id ,'全国')='全国','全国', group_concat(distinct uo.name)),'门店为空') 门店,
        if(COALESCE(lh.id ,'全国')='全国','全国',CONCAT(group_concat(distinct lh.id,''))) 房屋ID,
        COUNT(distinct bo.id) 离店订单数,
        count(distinct IF(ct.score>=5,bo.id,null)) 好评数,
        count(distinct IF(ct.score>=4 AND ct.score<5,bo.id,null)) 中评数,
        count(distinct IF(ct.score<4 and ct.content IS NOT null,bo.id,null)) 差评数,
        count(distinct IF(ct.content IS null ,bo.id,null)) 未评论数
        FROM youjia.orders bo
        LEFT JOIN youjia.order_detail od ON bo.id=od.order_id
        LEFT JOIN youjia.order_extend oe ON bo.id=oe.order_id
        LEFT JOIN youjia.lod_house lh ON lh.id=od.house_id
        LEFT JOIN youjia.city c ON c.id=lh.city_id
        LEFT JOIN youjia.user_org_service_center usc ON lh.service_id=usc.sid
        LEFT JOIN youjia_common.sales_channel sc ON sc.id=bo.order_channel
        LEFT JOIN youjia.comment ct ON bo.id=ct.order_id  AND ct.comment_type=0
        LEFT JOIN youjia.user_org uo ON lh.work_id=uo.id and uo.is_delete = 0
		left join youjia.user_org uo2 on uo2.id=uo.parent_id and uo2.is_delete = 0
		left join youjia.user_org uo3 on uo3.id=uo2.parent_id and uo3.is_delete = 0
        left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
        LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
        left join youjia.user_house_relation uhr2 on uhr2.house_id = lh.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
        LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
        WHERE  bo.city_id <> 376
        and bo.pay_state = 1
        and lh.own_type in {own_type}
        and bo.confirm_status=1
        AND bo.order_type = 0 
        AND bo.cancel_status=0 
        and bo.brush_status<>1
        and bo.order_channel in {channel_ids}
        AND DATE(bo.checkout_day) BETWEEN '{st}'  AND  '{et}'
        AND oe.tenant_mobile not LIKE '%转%'
        -- AND lh.service_id IS NOT NULL
        {sql_add}
        GROUP BY lh.own_type,lh.id
        with rollup""".format(st=st, et=et, own_type=own_type, channel_ids=channel_ids, sql_add=sql_add)
    # print(sql)
    # sql = """SELECT
    #     case group_concat(distinct h.own_type order by h.own_type)
    #         when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
    #     if(COALESCE(d.house_id ,'全国')='全国','全国', group_concat(distinct c.short_name))  城市,
    #     ifnull(if(COALESCE(d.house_id ,'全国')='全国','全国', group_concat(distinct org.name)),'门店为空') 门店,
    #     if(COALESCE(d.house_id ,'全国')='全国','全国',CONCAT(group_concat(distinct d.house_id,''))) 房屋ID,
    #     COUNT(distinct b.id) 离店订单数,
    #     count(distinct IF(t.score>=5,b.id,null)) 好评数,
    #     count(distinct IF(t.score>=4 AND t.score<5,b.id,null)) 中评数,
    #     count(distinct IF(t.score<4 and t.content IS NOT null,b.id,null)) 差评数,
    #     count(distinct IF(t.content IS null ,b.id,null)) 未评论数
    #     FROM orders b
    #     LEFT JOIN order_detail d ON b.id=d.order_id
    #     LEFT JOIN youjia.order_extend e ON b.id=e.order_id
    #     LEFT JOIN lod_house h ON d.house_id=h.id
    #     LEFT JOIN city c ON c.id=h.city_id
    #     LEFT JOIN user_org_service_center usc ON h.service_id=usc.sid
    #     LEFT JOIN youjia_common.sales_channel sc ON sc.id=b.order_channel
    #     LEFT JOIN youjia.`comment` t ON b.id=t.order_id  AND t.comment_type=0
    #     LEFT JOIN user_org org ON h.work_id=org.id and org.is_delete = 0
    #
    #     left join youjia.user_house_relation uhr1 on uhr1.house_id = h.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0
    #     LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
    #     left join youjia.user_house_relation uhr2 on uhr2.house_id = h.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0
    #     LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
    #     WHERE  b.city_id <> 376
    #     and b.pay_state = 1
    #     and h.own_type in {own_type}
    #     and b.confirm_status=1
    #     AND b.cancel_status=0 and b.brush_status<>1
    #     AND b.order_type in (0,2)
    #     and b.order_channel in {channel_ids}
    #     AND DATE(b.checkout_day) BETWEEN '{st}'  AND  '{et}'
    #     AND e.tenant_mobile not LIKE '%转%'
    #     -- AND h.service_id IS NOT NULL
    #
    #     GROUP BY h.own_type,d.house_id
    #     with rollup
    # """.format(st=st, et=et, own_type=own_type, channel_ids=channel_ids)
    # print(sql)
    return pd.read_sql_query(sql, db_util.con_youjia)


def brush_comment(st, et):
    """
    刷单订单评论明细
    :param st:
    :param et:
    :return:
    """
    sql = """select 
            case group_concat(distinct lh.own_type order by lh.own_type)
                when "1" then '有家' when "2" then '城宿' when "3" then '斯维登' else '所有公司' end 公司,
            city.short_name 城市,
            uo.name 门店,
            u1.realname 线上销售,
            u2.realname 线下管家,
            oe.tenant_name,
            oe.account_mobile,
            concat(bo.id,"") 有家订单ID,
            concat(od.house_id,"") 房屋ID,
            concat(bo.lodgeunit_id,"") 房源ID,
            bo.third_id 第三方订单ID,
            sc.name 渠道,
            date(bo.create_time) 下单时间,
            date(bo.checkin_day) 入住时间,
            date(bo.checkout_day) 离店时间,
            ct.create_time 评论时间, 
            ct.score 总分,
            ct.sanitation_s 房屋卫生,
            ct.service_s 管家服务,
            ct.traffic_s 交通位置,
            ct.facilities_s 室内设施,
            ct.environment_s 周边环境,
            ct.content 评论详情 ,
            ct2.content 房东回复内容
            from youjia.orders bo
            left join youjia.comment ct on ct.order_id = bo.id and ct.comment_type = 0 
            left join youjia.comment ct2 on ct2.order_id = bo.id and ct2.comment_type = 1
            left join youjia_common.sales_channel sc on sc.id = bo.order_channel
            left join youjia.order_detail od on od.order_id = bo.id
            left join youjia.order_extend oe on oe.order_id = bo.id 
            left join youjia.lod_house lh on lh.id = od.house_id
            left join youjia.user_org uo on uo.id = lh.work_id and uo.is_delete = 0
            -- left join youjia.user_org_service_center uosc on uosc.sid = lh.service_id
            left join youjia.user_house_relation uhr1 on uhr1.house_id = lh.id AND uhr1.user_role_id = 5 and uhr1.is_delete = 0 and uhr1.master = 1
            LEFT JOIN youjia.users u1 ON uhr1.user_id=u1.id
            left join youjia.user_house_relation uhr2 on uhr2.house_id = lh.id AND uhr2.user_role_id = 6 and uhr2.is_delete = 0 and uhr2.master = 1
            LEFT JOIN youjia.users u2 ON uhr2.user_id=u2.id
            left join youjia.city on city.id = bo.city_id
            where bo.brush_status = 1
            and bo.checkout_day between '{st}' and '{et}'
            and bo.city_id <> 376
            and bo.order_channel in (3,20,25)
            group  by bo.id
    """.format(st=st, et=et)
    return pd.read_sql_query(sql, db_util.con_youjia)


def comment_overview_month(st=None, et=None, own_type=(1, 2)):
    """
    评论数据总览 月累计
    :param st:
    :param et:
    :param own_type:

    COUNT(distinct b.id) 离店订单数,
    round(avg(t.score),2) 总分,
    count(distinct IF(t.score>=5,b.id,null)) 好评数,
    count(distinct IF(t.score>=4 AND t.score<5,b.id,null)) 中评数,
    count(distinct IF(t.score<4 and t.content IS NOT null,b.id,null)) 差评数,
    count(distinct IF(b.order_channel=3 AND t.content IS NOT null,b.id,null)) 途家评数,
    count(distinct IF(b.order_channel=20 AND t.content IS NOT null,b.id,null)) 爱彼迎评数,
    count(distinct IF(b.order_channel=25 AND t.content IS NOT null,b.id,null)) 榛果评数,
    count(distinct IF(t.content IS NOT null ,b.id,null)) 评论数,
    count(distinct IF(t.content IS null ,b.id,null)) 未评论数
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday
    log.info("评论数据总览 月累计 st={st},et={et}".format(st=st, et=et))
    # comment_overview_df = comment_overview_sql(st, et)
    all_order_comment_month_df = all_order_comment_month(st, et, own_type)

    total_df = all_order_comment_month_df[all_order_comment_month_df.门店 == '全国'].copy()

    best_df = total_df[total_df.公司 == "所有公司"].copy()

    other_df = all_order_comment_month_df[all_order_comment_month_df.门店 != '全国'].copy()
    all_order_comment_month_df = pd.concat([best_df, other_df])

    # 好评数	中评数	差评数	未评论数
    comment_overview_df = all_order_comment_month_df.groupby(['城市', '门店']).agg({'离店订单数': 'sum',
                                                                                '好评数': 'sum', '中评数': 'sum',
                                                                                '差评数': 'sum', '途家评数': 'sum',
                                                                                '爱彼迎评数': 'sum', '榛果评数': 'sum',
                                                                                '未评论数': 'sum', '途家好评数': 'sum',
                                                                                '爱彼迎好评数': 'sum',
                                                                                '榛果好评数': 'sum'})
    comment_overview_df['评论数'] = comment_overview_df['离店订单数'] - comment_overview_df['未评论数']
    comment_overview_df['好评率'] = comment_overview_df.apply(lambda row: percentage(row, '好评数', '评论数', False), axis=1)
    comment_overview_df['中评率'] = comment_overview_df.apply(lambda row: percentage(row, '中评数', '评论数', False), axis=1)
    comment_overview_df['差评率'] = comment_overview_df.apply(lambda row: percentage(row, '差评数', '离店订单数', False), axis=1)
    comment_overview_df['途家评论占比'] = comment_overview_df.apply(lambda row: percentage(row, '途家评数', '评论数', False), axis=1)
    comment_overview_df['爱彼迎评论占比'] = comment_overview_df.apply(lambda row: percentage(row, '爱彼迎评数', '评论数', False),
                                                               axis=1)
    comment_overview_df['榛果评论占比'] = comment_overview_df.apply(lambda row: percentage(row, '榛果评数', '评论数', False), axis=1)
    comment_overview_df['途家好评率'] = comment_overview_df.apply(lambda row: percentage(row, '途家好评数', '途家评数', False),
                                                             axis=1)
    comment_overview_df['爱彼迎好评率'] = comment_overview_df.apply(lambda row: percentage(row, '爱彼迎好评数', '爱彼迎评数', False),
                                                              axis=1)
    comment_overview_df['榛果好评率'] = comment_overview_df.apply(lambda row: percentage(row, '榛果好评数', '榛果评数', False),
                                                             axis=1)
    comment_overview_df['评论率'] = comment_overview_df.apply(lambda row: percentage(row, '评论数', '离店订单数', False), axis=1)
    comment_overview_df = comment_overview_df.reset_index()
    comment_overview_df = df_util.df_sort(comment_overview_df, '离店订单数')
    return comment_overview_df


# def comment_overview_no_company_month(st=None, et=None, own_type=(1, 2, 3)):
#     """
#     评论数据总览 月累计
#     :param st:
#     :param et:
#     :param own_type:
#
#     COUNT(distinct b.id) 离店订单数,
#     round(avg(t.score),2) 总分,
#     count(distinct IF(t.score>=5,b.id,null)) 好评数,
#     count(distinct IF(t.score>=4 AND t.score<5,b.id,null)) 中评数,
#     count(distinct IF(t.score<4 and t.content IS NOT null,b.id,null)) 差评数,
#     count(distinct IF(b.order_channel=3 AND t.content IS NOT null,b.id,null)) 途家评数,
#     count(distinct IF(b.order_channel=20 AND t.content IS NOT null,b.id,null)) 爱彼迎评数,
#     count(distinct IF(b.order_channel=25 AND t.content IS NOT null,b.id,null)) 榛果评数,
#     count(distinct IF(t.content IS NOT null ,b.id,null)) 评论数,
#     count(distinct IF(t.content IS null ,b.id,null)) 未评论数
#     :return:
#     """
#     yesterday = date_util.curdate()
#     st = st if st else date_util.cur_month_first(1, yesterday)
#     et = et if et else yesterday
#     log.info("评论数据总览 月累计 st={st},et={et}".format(st=st, et=et))
#     # comment_overview_df = comment_overview_sql(st, et)
#     all_order_comment_month_df = all_order_comment_month(st, et, own_type)
#     # 好评数	中评数	差评数	未评论数
#     comment_overview_df = all_order_comment_month_df.groupby(['城市', '门店']).agg({'离店订单数': 'sum',
#                                                                                 '好评数': 'sum', '中评数': 'sum',
#                                                                                 '差评数': 'sum', '途家评数': 'sum',
#                                                                                 '爱彼迎评数': 'sum', '榛果评数': 'sum',
#                                                                                 '未评论数': 'sum', '途家好评数': 'sum',
#                                                                                 '爱彼迎好评数': 'sum',
#                                                                                 '榛果好评数': 'sum'})
#     comment_overview_df['评论数'] = comment_overview_df['离店订单数'] - comment_overview_df['未评论数']
#     comment_overview_df['好评率'] = comment_overview_df.apply(lambda row: percentage(row, '好评数', '评论数', False), axis=1)
#     comment_overview_df['中评率'] = comment_overview_df.apply(lambda row: percentage(row, '中评数', '评论数', False), axis=1)
#     comment_overview_df['差评率'] = comment_overview_df.apply(lambda row: percentage(row, '差评数', '离店订单数', False), axis=1)
#     comment_overview_df['途家评论占比'] = comment_overview_df.apply(lambda row: percentage(row, '途家评数', '评论数', False), axis=1)
#     comment_overview_df['爱彼迎评论占比'] = comment_overview_df.apply(lambda row: percentage(row, '爱彼迎评数', '评论数', False),
#                                                                axis=1)
#     comment_overview_df['榛果评论占比'] = comment_overview_df.apply(lambda row: percentage(row, '榛果评数', '评论数', False), axis=1)
#     comment_overview_df['途家好评率'] = comment_overview_df.apply(lambda row: percentage(row, '途家好评数', '途家评数', False),
#                                                              axis=1)
#     comment_overview_df['爱彼迎好评率'] = comment_overview_df.apply(lambda row: percentage(row, '爱彼迎好评数', '爱彼迎评数', False),
#                                                               axis=1)
#     comment_overview_df['榛果好评率'] = comment_overview_df.apply(lambda row: percentage(row, '榛果好评数', '榛果评数', False),
#                                                              axis=1)
#     comment_overview_df['评论率'] = comment_overview_df.apply(lambda row: percentage(row, '评论数', '离店订单数', False), axis=1)
#     comment_overview_df = comment_overview_df.reset_index()
#     return comment_overview_df


# def comment_overview_city_month(st=None, et=None, own_type=(1, 2, 3)):
#     """
#     评论数据总览 月累计
#     :param st:
#     :param et:
#     :param own_type:
#     :return:
#     """
#     yesterday = date_util.curdate()
#     st = st if st else date_util.cur_month_first(1, yesterday)
#     et = et if et else yesterday
#     log.info("评论数据总览 月累计 st={st},et={et}".format(st=st, et=et))
#     # comment_overview_df = comment_overview_sql(st, et)
#     all_order_comment_month_df = all_order_comment_month(st, et, own_type)
#     # 好评数	中评数	差评数	未评论数
#     comment_overview_df = all_order_comment_month_df.groupby(['公司', '城市']).agg({'离店订单数': 'sum',
#                                                                                 '好评数': 'sum', '中评数': 'sum',
#                                                                                 '差评数': 'sum', '途家评数': 'sum',
#                                                                                 '爱彼迎评数': 'sum', '榛果评数': 'sum',
#                                                                                 '未评论数': 'sum', '途家好评数': 'sum',
#                                                                                 '爱彼迎好评数': 'sum',
#                                                                                 '榛果好评数': 'sum'})
#     comment_overview_df['评论数'] = comment_overview_df['离店订单数'] - comment_overview_df['未评论数']
#     comment_overview_df['好评率'] = comment_overview_df.apply(lambda row: percentage(row, '好评数', '评论数', False), axis=1)
#     comment_overview_df['中评率'] = comment_overview_df.apply(lambda row: percentage(row, '中评数', '评论数', False), axis=1)
#     comment_overview_df['差评率'] = comment_overview_df.apply(lambda row: percentage(row, '差评数', '离店订单数', False), axis=1)
#     comment_overview_df['途家评论占比'] = comment_overview_df.apply(lambda row: percentage(row, '途家评数', '评论数', False), axis=1)
#     comment_overview_df['爱彼迎评论占比'] = comment_overview_df.apply(lambda row: percentage(row, '爱彼迎评数', '评论数', False),
#                                                                axis=1)
#     comment_overview_df['榛果评论占比'] = comment_overview_df.apply(lambda row: percentage(row, '榛果评数', '评论数', False), axis=1)
#     comment_overview_df['途家好评率'] = comment_overview_df.apply(lambda row: percentage(row, '途家好评数', '途家评数', False),
#                                                              axis=1)
#     comment_overview_df['爱彼迎好评率'] = comment_overview_df.apply(lambda row: percentage(row, '爱彼迎好评数', '爱彼迎评数', False),
#                                                               axis=1)
#     comment_overview_df['榛果好评率'] = comment_overview_df.apply(lambda row: percentage(row, '榛果好评数', '榛果评数', False),
#                                                              axis=1)
#     comment_overview_df['评论率'] = comment_overview_df.apply(lambda row: percentage(row, '评论数', '离店订单数', False), axis=1)
#     comment_overview_df = comment_overview_df.reset_index()
#     return comment_overview_df


# def comment_overview_ago_seven(st=None, et=None, own_type=(1, 2, 3)):
#     """
#     评论数据总览 前七天
#     :param st:
#     :param et:
#     :param own_type:
#     :return:
#     """
#     yesterday = date_util.curdate()
#     st = st if st else date_util.date_sub(yesterday, 6)
#     et = et if et else yesterday
#     log.info("评论数据总览 前七天 st={st},et={et}".format(st=st, et=et))
#     # comment_overview_df = comment_overview_sql(st, et)
#
#     all_order_comment_ago_seven_df = all_order_comment_ago_seven(st, et, own_type)
#     # 好评数	中评数	差评数	未评论数
#     comment_overview_df = all_order_comment_ago_seven_df.groupby(['城市', '门店']).agg({'离店订单数': 'sum',
#                                                                                     '好评数': 'sum', '中评数': 'sum',
#                                                                                     '差评数': 'sum', '途家评数': 'sum',
#                                                                                     '爱彼迎评数': 'sum', '榛果评数': 'sum',
#                                                                                     '未评论数': 'sum', '途家好评数': 'sum',
#                                                                                     '爱彼迎好评数': 'sum',
#                                                                                     '榛果好评数': 'sum'})
#     comment_overview_df['评论数'] = comment_overview_df['离店订单数'] - comment_overview_df['未评论数']
#     comment_overview_df['好评率'] = comment_overview_df.apply(lambda row: percentage(row, '好评数', '评论数', False), axis=1)
#     comment_overview_df['中评率'] = comment_overview_df.apply(lambda row: percentage(row, '中评数', '评论数', False), axis=1)
#     comment_overview_df['差评率'] = comment_overview_df.apply(lambda row: percentage(row, '差评数', '离店订单数', False), axis=1)
#     comment_overview_df['途家评论占比'] = comment_overview_df.apply(lambda row: percentage(row, '途家评数', '评论数', False), axis=1)
#     comment_overview_df['爱彼迎评论占比'] = comment_overview_df.apply(lambda row: percentage(row, '爱彼迎评数', '评论数', False),
#                                                                axis=1)
#     comment_overview_df['榛果评论占比'] = comment_overview_df.apply(lambda row: percentage(row, '榛果评数', '评论数', False), axis=1)
#     comment_overview_df['途家好评率'] = comment_overview_df.apply(lambda row: percentage(row, '途家好评数', '途家评数', False),
#                                                              axis=1)
#     comment_overview_df['爱彼迎好评率'] = comment_overview_df.apply(lambda row: percentage(row, '爱彼迎好评数', '爱彼迎评数', False),
#                                                               axis=1)
#     comment_overview_df['榛果好评率'] = comment_overview_df.apply(lambda row: percentage(row, '榛果好评数', '榛果评数', False),
#                                                              axis=1)
#     comment_overview_df['评论率'] = comment_overview_df.apply(lambda row: percentage(row, '评论数', '离店订单数', False), axis=1)
#     comment_overview_df = comment_overview_df.reset_index()
#     return comment_overview_df


def comment_overview_house_channel(st=None, et=None, own_type=(1, 2), channel_ids=[3, 20, 25], have_brush=False):
    """
    传入渠道只取这个渠道数据房屋维度
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :param have_brush:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday
    log.info("评论数据总览 前七天 st={st},et={et}".format(st=st, et=et))
    # comment_overview_df = comment_overview_sql(st, et)

    all_order_comment_house_df = all_order_comment(st, et, own_type, channel_ids=channel_ids, have_brush=have_brush)
    # 好评数	中评数	差评数	未评论数
    all_order_comment_house_df = df_util.df_rename(all_order_comment_house_df, {"房屋id": "房屋ID"})
    all_order_comment_house_df = all_order_comment_house_df[all_order_comment_house_df.房屋ID != '全国']
    comment_overview_df = all_order_comment_house_df.groupby(['房屋ID']).agg({'离店订单数': 'sum', '好评数': 'sum',
                                                                            '差评数': 'sum', '未评论数': 'sum'})
    comment_overview_df['评论数'] = comment_overview_df['离店订单数'] - comment_overview_df['未评论数']
    comment_overview_df['好评率'] = comment_overview_df.apply(lambda row: percentage(row, '好评数', '评论数', False), axis=1)
    comment_overview_df['评论率'] = comment_overview_df.apply(lambda row: percentage(row, '评论数', '离店订单数', False), axis=1)
    comment_overview_df['差评率'] = comment_overview_df.apply(lambda row: percentage(row, '差评数', '离店订单数', False), axis=1)
    return comment_overview_df


def comment_overview_online_sale_channel(st=None, et=None, own_type=(1, 2), channel_ids=[3, 20, 25], have_brush=False):
    """
    传入渠道只取这个渠道数据房屋维度
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :param have_brush:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday
    log.info("评论数据总览 前七天 st={st},et={et}".format(st=st, et=et))
    # comment_overview_df = comment_overview_sql(st, et)

    all_order_comment_house_df = all_order_comment(st, et, own_type, channel_ids=channel_ids, have_brush=have_brush)
    # 好评数	中评数	差评数	未评论数
    all_order_comment_house_df = df_util.df_rename(all_order_comment_house_df, {"房屋id": "房屋ID"})
    all_order_comment_house_df = all_order_comment_house_df[all_order_comment_house_df.房屋ID != '全国']
    comment_overview_df = all_order_comment_house_df.groupby(['线上销售']).agg({'离店订单数': 'sum', '好评数': 'sum',
                                                                            '差评数': 'sum', '未评论数': 'sum'})
    comment_overview_df['评论数'] = comment_overview_df['离店订单数'] - comment_overview_df['未评论数']
    comment_overview_df['好评率'] = comment_overview_df.apply(lambda row: percentage(row, '好评数', '评论数', False), axis=1)
    comment_overview_df['评论率'] = comment_overview_df.apply(lambda row: percentage(row, '评论数', '离店订单数', False), axis=1)
    comment_overview_df['差评率'] = comment_overview_df.apply(lambda row: percentage(row, '差评数', '离店订单数', False), axis=1)
    return comment_overview_df


def comment_overview_offline_sale_channel(st=None, et=None, own_type=(1, 2), channel_ids=[3, 20, 25], have_brush=False):
    """
    传入渠道只取这个渠道数据房屋维度
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :param have_brush:
    :return:
    """
    # yesterday = date_util.curdate()
    # st = st if st else date_util.date_sub(yesterday, 6)
    # et = et if et else yesterday
    # log.info("评论数据总览 前七天 st={st},et={et}".format(st=st, et=et))
    # comment_overview_df = comment_overview_sql(st, et)

    all_order_comment_house_df = all_order_comment(st, et, own_type, channel_ids=channel_ids, have_brush=have_brush)
    # 好评数	中评数	差评数	未评论数
    all_order_comment_house_df = df_util.df_rename(all_order_comment_house_df, {"房屋id": "房屋ID"})
    all_order_comment_house_df = all_order_comment_house_df[all_order_comment_house_df.房屋ID != '全国']
    comment_overview_df = all_order_comment_house_df.groupby(['线下管家']).agg({'离店订单数': 'sum', '好评数': 'sum',
                                                                            '差评数': 'sum', '未评论数': 'sum'})
    comment_overview_df['评论数'] = comment_overview_df['离店订单数'] - comment_overview_df['未评论数']
    comment_overview_df['好评率'] = comment_overview_df.apply(lambda row: percentage(row, '好评数', '评论数', False), axis=1)
    comment_overview_df['评论率'] = comment_overview_df.apply(lambda row: percentage(row, '评论数', '离店订单数', False), axis=1)
    comment_overview_df['差评率'] = comment_overview_df.apply(lambda row: percentage(row, '差评数', '离店订单数', False), axis=1)
    return comment_overview_df


def comment_overview_work_channel_is_brush(st=None, et=None, own_type=(1, 2, 3), channel_ids=[3, 20, 25],
                                           have_brush=False):
    """
    传入渠道只取这个渠道数据城市维度
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :param have_brush:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday
    log.info("评论数据总览 前七天 st={st},et={et}".format(st=st, et=et))
    # comment_overview_df = comment_overview_sql(st, et)

    all_order_comment_house_df = all_order_comment(st, et, own_type, channel_ids=channel_ids, have_brush=have_brush)

    total_df = all_order_comment_house_df[all_order_comment_house_df.门店 == '全国'].copy()

    best_df = total_df[total_df.公司 == "所有公司"].copy()

    other_df = all_order_comment_house_df[all_order_comment_house_df.门店 != '全国'].copy()
    all_order_comment_house_df = pd.concat([best_df, other_df])

    # 好评数	中评数	差评数	未评论数
    comment_overview_df = all_order_comment_house_df.groupby(['城市', '门店']).agg({'离店订单数': 'sum',
                                                                                '好评数': 'sum', '中评数': 'sum',
                                                                                '差评数': 'sum', '未评论数': 'sum'})
    comment_overview_df['评论数'] = comment_overview_df['离店订单数'] - comment_overview_df['未评论数']
    comment_overview_df['好评率'] = comment_overview_df.apply(lambda row: percentage(row, '好评数', '评论数', False), axis=1)
    comment_overview_df['评论率'] = comment_overview_df.apply(lambda row: percentage(row, '评论数', '离店订单数', False), axis=1)
    comment_overview_df['差评率'] = comment_overview_df.apply(lambda row: percentage(row, '差评数', '离店订单数', False), axis=1)
    return comment_overview_df


def comment_overview_work_channel(st=None, et=None, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    no_brush_df = comment_overview_work_channel_is_brush(st=st, et=et, own_type=own_type,
                                                         channel_ids=channel_ids,
                                                         have_brush=False)
    have_brush_df = comment_overview_work_channel_is_brush(st=st, et=et, own_type=own_type,
                                                           channel_ids=channel_ids,
                                                           have_brush=True)
    have_brush_df = have_brush_df[["离店订单数", "评论数", "评论率"]].copy()
    have_brush_df = df_util.df_rename(have_brush_df, {"离店订单数": "离店订单数（含刷单）", "评论数": "评论数（含刷单）",
                                                      "评论率": "评论率（含刷单）"})

    df = pd.merge(no_brush_df, have_brush_df, how="left", left_index=True, right_index=True)
    return df


def comment_overview_city_channel_is_brush(st=None, et=None, own_type=(1, 2, 3), channel_ids=[3, 20, 25],
                                           have_brush=False):
    """
    传入渠道只取这个渠道数据城市维度
    :param st:
    :param et:
    :param own_type:
    :param channel_ids:
    :param have_brush:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday
    log.info("评论数据总览 前七天 st={st},et={et}".format(st=st, et=et))
    # comment_overview_df = comment_overview_sql(st, et)

    all_order_comment_house_df = all_order_comment(st, et, own_type, channel_ids=channel_ids, have_brush=have_brush)
    # 好评数	中评数	差评数	未评论数
    comment_overview_df = all_order_comment_house_df.groupby(['公司', '城市']).agg({'离店订单数': 'sum', '好评数': 'sum',
                                                                                '差评数': 'sum', '未评论数': 'sum'})
    comment_overview_df['评论数'] = comment_overview_df['离店订单数'] - comment_overview_df['未评论数']
    comment_overview_df['好评率'] = comment_overview_df.apply(lambda row: percentage(row, '好评数', '评论数', False), axis=1)
    comment_overview_df['评论率'] = comment_overview_df.apply(lambda row: percentage(row, '评论数', '离店订单数', False), axis=1)
    comment_overview_df['差评率'] = comment_overview_df.apply(lambda row: percentage(row, '差评数', '离店订单数', False), axis=1)
    return comment_overview_df


def comment_overview_city_channel(st=None, et=None, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    no_brush_df = comment_overview_city_channel_is_brush(st=st, et=et, own_type=own_type,
                                                         channel_ids=channel_ids,
                                                         have_brush=False)
    have_brush_df = comment_overview_city_channel_is_brush(st=st, et=et, own_type=own_type,
                                                           channel_ids=channel_ids,
                                                           have_brush=True)
    have_brush_df = have_brush_df[["离店订单数", "评论数", "评论率"]].copy()
    have_brush_df = df_util.df_rename(have_brush_df, {"离店订单数": "离店订单数（含刷单）", "评论数": "评论数（含刷单）",
                                                      "评论率": "评论率（含刷单）"})

    df = pd.merge(no_brush_df, have_brush_df, how="left", left_index=True, right_index=True)
    return df


def comment_overview_district_channel_is_brush(st=None, et=None, own_type=(1, 2, 3), channel_ids=[3, 20, 25],
                                               have_brush=False):
    """
        传入渠道只取这个渠道数据城市维度
        :param st:
        :param et:
        :param own_type:
        :param channel_ids:
        :param have_brush:
        :return:
        """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday
    log.info("评论数据总览 前七天 st={st},et={et}".format(st=st, et=et))
    # comment_overview_df = comment_overview_sql(st, et)

    all_order_comment_house_df = all_order_comment(st, et, own_type, channel_ids=channel_ids, have_brush=have_brush)

    total_df = all_order_comment_house_df[all_order_comment_house_df.门店 == '全国'].copy()

    best_df = total_df[total_df.公司 == "所有公司"].copy()

    other_df = all_order_comment_house_df[all_order_comment_house_df.门店 != '全国'].copy()
    all_order_comment_house_df = pd.concat([best_df, other_df])
    # 好评数	中评数	差评数	未评论数
    comment_overview_df = all_order_comment_house_df.groupby(['大区']).agg({'离店订单数': 'sum', '好评数': 'sum',
                                                                          '差评数': 'sum', '未评论数': 'sum'})
    comment_overview_df['评论数'] = comment_overview_df['离店订单数'] - comment_overview_df['未评论数']
    comment_overview_df['好评率'] = comment_overview_df.apply(lambda row: percentage(row, '好评数', '评论数', False), axis=1)
    comment_overview_df['评论率'] = comment_overview_df.apply(lambda row: percentage(row, '评论数', '离店订单数', False), axis=1)
    comment_overview_df['差评率'] = comment_overview_df.apply(lambda row: percentage(row, '差评数', '离店订单数', False), axis=1)
    return comment_overview_df


def comment_overview_district_channel(st=None, et=None, own_type=(1, 2, 3), channel_ids=[3, 20, 25]):
    no_brush_df = comment_overview_district_channel_is_brush(st=st, et=et, own_type=own_type,
                                                             channel_ids=channel_ids,
                                                             have_brush=False)
    have_brush_df = comment_overview_district_channel_is_brush(st=st, et=et, own_type=own_type,
                                                               channel_ids=channel_ids,
                                                               have_brush=True)
    have_brush_df = have_brush_df[["离店订单数", "评论数", "评论率"]].copy()
    have_brush_df = df_util.df_rename(have_brush_df, {"离店订单数": "离店订单数（含刷单）", "评论数": "评论数（含刷单）",
                                                      "评论率": "评论率（含刷单）"})

    df = pd.merge(no_brush_df, have_brush_df, how="left", left_index=True, right_index=True)
    return df


def bad_comment_yesterday(st=None, et=None):
    """
    差评 昨日数据
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday
    log.info("差评 昨日数据 st={st},et={et}".format(st=st, et=et))
    bad_comment_df = bad_comment(st, et)
    return bad_comment_df


def bad_comment_month(st=None, et=None):
    """
    差评 月累计
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday
    log.info("差评 月累计 st={st},et={et}".format(st=st, et=et))
    bad_comment_df = bad_comment(st, et)
    return bad_comment_df


def bad_comment_ago_seven(st=None, et=None):
    """
    差评 前七天
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday
    log.info("差评 前七天 st={st},et={et}".format(st=st, et=et))
    bad_comment_df = bad_comment(st, et)
    return bad_comment_df


def good_comment_yesterday(st=None, et=None):
    """
    好评 昨日数据
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday
    log.info("好评 昨日数据 st={st},et={et}".format(st=st, et=et))
    good_comment_df = good_comment(st, et)
    return good_comment_df


def good_comment_month(st=None, et=None):
    """
    好评 月累计
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday
    log.info("好评 月累计 st={st},et={et}".format(st=st, et=et))
    good_comment_df = good_comment(st, et)
    return good_comment_df


def good_comment_ago_seven(st=None, et=None):
    """
    好评 前七天
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday
    log.info("好评 前七天 st={st},et={et}".format(st=st, et=et))
    good_comment_df = good_comment(st, et)
    return good_comment_df


def no_comment_yesterday(st=None, et=None):
    """
    离店未评价明细 昨日数据
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday
    log.info("好评 昨日数据 st={st},et={et}".format(st=st, et=et))
    no_comment_df = no_comment(st, et)
    return no_comment_df


def no_comment_month(st=None, et=None):
    """
    离店未评价明细 月累计
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday
    log.info("好评 月累计 st={st},et={et}".format(st=st, et=et))
    no_comment_df = no_comment(st, et)
    return no_comment_df


def no_comment_ago_seven(st=None, et=None):
    """
    离店未评价明细 前七天
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday
    log.info("好评 前七天 st={st},et={et}".format(st=st, et=et))
    no_comment_df = no_comment(st, et)
    return no_comment_df


def all_order_comment_month(st=None, et=None, own_type=(1, 2), have_brush=False):
    """
    所有离店订单评论明细 月累计
    :param st:
    :param et:
    :param own_type:
    :param have_brush:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday
    log.info("好评 月累计 st={st},et={et}".format(st=st, et=et))
    all_order_comment_df = all_order_comment(st, et, own_type, have_brush=have_brush)
    return all_order_comment_df


#
# def all_order_comment_ago_seven(st=None, et=None, own_type=(1, 2, 3)):
#     """
#     所有离店订单评论明细 前七天
#     :param st:
#     :param et:
#     :param own_type:
#     :return:
#     """
#     yesterday = date_util.curdate()
#     st = st if st else date_util.date_sub(yesterday, 6)
#     et = et if et else yesterday
#     log.info("好评 前七天 st={st},et={et}".format(st=st, et=et))
#     all_order_comment_df = all_order_comment(st, et, own_type)
#     return all_order_comment_df


def brush_comment_yesterday(st=None, et=None, own_type=(1, 2, 3)):
    """
    所有刷单评论明细 昨日数据
    :param st:
    :param et:
    :param own_type:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else yesterday
    et = et if et else yesterday
    log.info("刷单 昨日数据 st={st},et={et}".format(st=st, et=et))
    brush_comment_df = brush_comment(st, et)
    return brush_comment_df


def brush_comment_month(st=None, et=None):
    """
    所有刷单评论明细 月累计
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.cur_month_first(1, yesterday)
    et = et if et else yesterday
    log.info("刷单 月累计 st={st},et={et}".format(st=st, et=et))
    brush_comment_df = brush_comment(st, et)
    return brush_comment_df


def brush_comment_ago_seven(st=None, et=None):
    """
    所有刷单评论明细 前七天
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 6)
    et = et if et else yesterday
    log.info("刷单 前七天 st={st},et={et}".format(st=st, et=et))
    brush_comment_df = brush_comment(st, et)
    return brush_comment_df


def brush_comment_ago_thirty(st=None, et=None):
    """
    所有刷单评论明细 前30天
    :param st:
    :param et:
    :return:
    """
    yesterday = date_util.curdate()
    st = st if st else date_util.date_sub(yesterday, 29)
    et = et if et else yesterday
    log.info("刷单 前30天 st={st},et={et}".format(st=st, et=et))
    brush_comment_df = brush_comment(st, et)
    return brush_comment_df


if __name__ == '__main__':
    pass
    df = comment_overview_district_channel('2019-12-01', '2019-12-10', own_type=(1, 2))
    # house = comment_overview_house_channel('2019-12-01', '2019-12-10', own_type=(1, 2))
    # work = comment_overview_work_channel('2019-12-01', '2019-12-10', own_type=(1, 2))
    # city = comment_overview_city_channel('2019-12-01', '2019-12-10', own_type=(1, 2))
    # district = comment_overview_district_channel('2019-12-01', '2019-12-10', own_type=(1, 2))
    from report_system.utils import excel_util

    excel_util.pd_to_excel({"df": df}, "df")
    # from report_system.utils import excel_util
    # excel_util.pd_to_excel({"df": df}, '00df')
    # print(df)
